import openpyxl
import pymysql
from utils.DButils import DButils
#1.创建表和sheet页
# workbook = openpyxl.Workbook()
# workbook.create_sheet(index=0,title='user_sheet')
# workbook.save('user.xlsx')



#2.写入数据
# workbook = openpyxl.load_workbook('user.xlsx')
# worksheet = workbook['user_sheet']
# for row in range(1, 2001):
#         for col in range(1, 11):
#             # 生成 1
#             worksheet.cell(row=row, column=col, value=1)
# workbook.save('user.xlsx')


#3.读取出来
class ReadExcel:
    @staticmethod
    def read_excel(file_name,sheet_name):
        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        data = [[sheet.cell(row=i,column=j).value for j in range(1,sheet.max_column+1)] for i in range(2,sheet.max_row+1)]
        return data


# if __name__ == '__main__':
#     db = DButils('127.0.0.1', 'root', '123456', 'pythondbutils')
#     sql = "insert into user_test values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#     data = ReadExcel.read_excel('user.xlsx','user_sheet')
#     db.many_upgrade(sql,data)


# import openpyxl
# import pymysql
# from DButils import DButils
# from FileUntils import ReadExcel
# #1.
# # wb = openpyxl.Workbook()
# # ws = wb.create_sheet(title='user')
#
# #2.
# # wb = openpyxl.load_workbook('test2.xlsx')
# # ws = wb['user']
# # list1 = ['吕布',22,'男',174]
# # ws.append(list1)
# # wb.save('test2.xlsx')
# # print('写入完成')
#
# #3.
# wb = openpyxl.load_workbook('test2.xlsx')
# ws = wb['user']
# db = DButils('127.0.0.1', 'root', '123456', 'pythondbutils')
# sql = "insert into person values (%s,%s,%s,%s)"
# data = ReadExcel.read_excel('test2.xlsx','user')
# db.many_upgrade(sql,data)
#
#cbcacbaaabec